import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * string.xml语言包与excel之间的转换
 * todo string-array 还不支持
 * xml : https://dom4j.github.io/
 * excel : https://sourceforge.net/projects/jexcelapi/
 */
public class XmlExcel {

    public static void main(String[] args) {
//        xmlToExcel();
        excelToXml();
    }

    /**
     * string.xml ->excel
     */
    public static void xmlToExcel() {
        try {
            Map<String, String> tempMap = new HashMap<>();// name value
            List<String> names = new ArrayList<>();//name的顺序
            SAXReader saxReader = new SAXReader();
            //生成excel文件路径
            String excelPath = "C:\\Users\\Administrator\\Desktop\\String2.xls";
            //原string.xml文件目录
            String path = "C:\\Users\\Administrator\\Desktop\\string\\";
            //基准翻译
            String defPath = "C:\\Users\\Administrator\\Desktop\\string\\strings.xml";
            File excelFile = new File(excelPath);
            if (!excelFile.exists()) {
                excelFile.createNewFile();
            }
            //写excel的类
            WritableWorkbook book = Workbook.createWorkbook(excelFile);
            // 新建一个sheet
            WritableSheet writableSheet = book.createSheet("one", 0);

            //先写第一第二列excel
            Document document = saxReader.read(defPath);
            Element employees = document.getRootElement();
            int j = 0;
            for (Iterator i = employees.elementIterator(); i.hasNext(); j++) {
                Element element = (Element) i.next();

                //todo  array待解析
                String elementName = element.getName();
                if (false&&elementName.equals("string-array")) {
                    Iterator<Element> iterator = element.elementIterator();
                    int subIndex = 0;
                    while (iterator.hasNext()) {
                        Element arrayElement = iterator.next();
                        String name = element.attributeValue("name") + "==" + subIndex;
                        names.add(name);
                        WritableCell attribute = new Label(0, j, name);
                        WritableCell values = new Label(1, j, arrayElement.getStringValue());
                        // 往表中添加内容
                        writableSheet.addCell(attribute);
                        writableSheet.addCell(values);
                        subIndex++;
                    }


                } else {
                    names.add(element.attributeValue("name"));
                    WritableCell attribute = new Label(0, j, element.attributeValue("name"));
                    WritableCell values = new Label(1, j, element.getStringValue());
                    // 往表中添加内容
                    writableSheet.addCell(attribute);
                    writableSheet.addCell(values);
                }

            }

            //写其他列excel
            int column = 2;
            File inputXml = new File(path);
            String[] fileList = inputXml.list();
            for (int a = 0; a < fileList.length; a++) {
                String name = fileList[a];
                System.out.println(path + name);
                if (!defPath.equals(path + name)) {
                    Document document2 = saxReader.read(path + name);
                    Element employees2 = document2.getRootElement();
                    int jj = 0;
                    for (Iterator i = employees2.elementIterator(); i.hasNext(); jj++) {
                        Element employee = (Element) i.next();
                        tempMap.put(employee.attributeValue("name"), employee.getStringValue());//更新翻译
                    }
                    for (int r = 0; r < names.size(); r++) {
                        String value = tempMap.get(names.get(r));
                        WritableCell values = new Label(column, r, value);
                        // 往表中添加内容
                        writableSheet.addCell(values);
                    }
                    column++;
                    tempMap.clear();
                }
            }
            // 将数据写入所建的excel
            book.write();
            book.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } catch (DocumentException e) {
            e.printStackTrace();
        }
    }

    /**
     * excel ->string.xml
     */
    public static void excelToXml() {
        String excelPath = "C:\\Users\\Administrator\\Desktop\\String2.xls";
        String outputPath = "C:\\Users\\Administrator\\Desktop\\";
        List<File> outputFiles = new ArrayList<>();
        try {
            Workbook workbook = Workbook.getWorkbook(new File(excelPath));
            Sheet sheet = workbook.getSheet(0);
            //创建语言包
            for (int c = 1; c < sheet.getColumns(); c++) {
                File file = new File(outputPath + c + ".xml");
                if (file.exists()) {
                    file.delete();
                }
                if (!file.exists()) {
                    file.createNewFile();
                }
                outputFiles.add(file);
            }
            //写文件
            for (int j = 0; j < sheet.getRows(); j++) {
                Cell[] cells = sheet.getRow(j);//第几行
                Cell c0 = cells[0];
                if (cells.length > 1) {
                    Cell c1 = cells[1];
                    File file = outputFiles.get(0);
                    FileOutputStream fos = new FileOutputStream(file, true);
                    fos.write(("<string name=\"" + c0.getContents() + "\">" + c1.getContents() + "</string>\n").getBytes("utf-8"));
                    fos.flush();
                }
                if (cells.length > 2) {
                    Cell c2 = cells[2];
                    File file = outputFiles.get(1);
                    FileOutputStream fos = new FileOutputStream(file, true);
                    fos.write(("<string name=\"" + c0.getContents() + "\">" + c2.getContents() + "</string>\n").getBytes("utf-8"));
                    fos.flush();
                }
                if (cells.length > 3) {
                    Cell c3 = cells[3];
                    File file = outputFiles.get(2);
                    FileOutputStream fos = new FileOutputStream(file, true);
                    fos.write(("<string name=\"" + c0.getContents() + "\">" + c3.getContents() + "</string>\n").getBytes("utf-8"));
                    fos.flush();
                }
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
    }
}
